--    Program: 		CAPPOIMP_PERSON.sql
--    Description:  Import PO Person (Employee) information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Francis Lee
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_person_id				number;
   
   cursor cur_persons is
      select * from CRST_PER_ALL_PEOPLE_F
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_persons loop   	
       l_err_msg_tmp := '';
       
       select PER_PEOPLE_S.NEXTVAL
   	   into   l_person_id
   	   from   dual;
   	   
   	   update CRST_PER_ALL_PEOPLE_F
   	   set    person_id_n = l_person_id
   	   where  person_id = cur.person_id;
   	 
       begin
         -- import data
         insert into PER_ALL_PEOPLE_F
         (
           PERSON_ID                      ,
           EFFECTIVE_START_DATE           ,
           EFFECTIVE_END_DATE             ,
           BUSINESS_GROUP_ID              ,
           PERSON_TYPE_ID                 ,
           LAST_NAME                      ,
           START_DATE                     ,
           APPLICANT_NUMBER               ,
           COMMENT_ID                     ,
           CURRENT_APPLICANT_FLAG         ,
           CURRENT_EMP_OR_APL_FLAG        ,
           CURRENT_EMPLOYEE_FLAG          ,
           DATE_EMPLOYEE_DATA_VERIFIED    ,
           DATE_OF_BIRTH                  ,
           EMAIL_ADDRESS                  ,
           EMPLOYEE_NUMBER                ,
           EXPENSE_CHECK_SEND_TO_ADDRESS  ,
           FIRST_NAME                     ,
           FULL_NAME                      ,
           KNOWN_AS                       ,
           MARITAL_STATUS                 ,
           MIDDLE_NAMES                   ,
           NATIONALITY                    ,
           NATIONAL_IDENTIFIER            ,
           PREVIOUS_LAST_NAME             ,
           REGISTERED_DISABLED_FLAG       ,
           SEX                            ,
           TITLE                          ,
           VENDOR_ID                      ,
           WORK_TELEPHONE                 ,
           REQUEST_ID                     ,
           PROGRAM_APPLICATION_ID         ,
           PROGRAM_ID                     ,
           PROGRAM_UPDATE_DATE            ,
           ATTRIBUTE_CATEGORY             ,
           ATTRIBUTE1                     ,
           ATTRIBUTE2                     ,
           ATTRIBUTE3                     ,
           ATTRIBUTE4                     ,
           ATTRIBUTE5                     ,
           ATTRIBUTE6                     ,
           ATTRIBUTE7                     ,
           ATTRIBUTE8                     ,
           ATTRIBUTE9                     ,
           ATTRIBUTE10                    ,
           ATTRIBUTE11                    ,
           ATTRIBUTE12                    ,
           ATTRIBUTE13                    ,
           ATTRIBUTE14                    ,
           ATTRIBUTE15                    ,
           ATTRIBUTE16                    ,
           ATTRIBUTE17                    ,
           ATTRIBUTE18                    ,
           ATTRIBUTE19                    ,
           ATTRIBUTE20                    ,
           ATTRIBUTE21                    ,
           ATTRIBUTE22                    ,
           ATTRIBUTE23                    ,
           ATTRIBUTE24                    ,
           ATTRIBUTE25                    ,
           ATTRIBUTE26                    ,
           ATTRIBUTE27                    ,
           ATTRIBUTE28                    ,
           ATTRIBUTE29                    ,
           ATTRIBUTE30                    ,
           LAST_UPDATE_DATE               ,
           LAST_UPDATED_BY                ,
           LAST_UPDATE_LOGIN              ,
           CREATED_BY                     ,
           CREATION_DATE                  ,
           PER_INFORMATION_CATEGORY       ,
           PER_INFORMATION1               ,
           PER_INFORMATION2               ,
           PER_INFORMATION3               ,
           PER_INFORMATION4               ,
           PER_INFORMATION5               ,
           PER_INFORMATION6               ,
           PER_INFORMATION7               ,
           PER_INFORMATION8               ,
           PER_INFORMATION9               ,
           PER_INFORMATION10              ,
           PER_INFORMATION11              ,
           PER_INFORMATION12              ,
           PER_INFORMATION13              ,
           PER_INFORMATION14              ,
           PER_INFORMATION15              ,
           PER_INFORMATION16              ,
           PER_INFORMATION17              ,
           PER_INFORMATION18              ,
           PER_INFORMATION19              ,
           PER_INFORMATION20              ,
           BACKGROUND_CHECK_STATUS        ,
           BACKGROUND_DATE_CHECK          ,
           BLOOD_TYPE                     ,
           CORRESPONDENCE_LANGUAGE        ,
           FAST_PATH_EMPLOYEE             ,
           FTE_CAPACITY                   ,
           HOLD_APPLICANT_DATE_UNTIL      ,
           HONORS                         ,
           INTERNAL_LOCATION              ,
           LAST_MEDICAL_TEST_BY           ,
           LAST_MEDICAL_TEST_DATE         ,
           MAILSTOP                       ,
           OFFICE_NUMBER                  ,
           ON_MILITARY_SERVICE            ,
           ORDER_NAME                     ,
           PRE_NAME_ADJUNCT               ,
           PROJECTED_START_DATE           ,
           REHIRE_AUTHORIZOR              ,
           REHIRE_RECOMMENDATION          ,
           RESUME_EXISTS                  ,
           RESUME_LAST_UPDATED            ,
           SECOND_PASSPORT_EXISTS         ,
           STUDENT_STATUS                 ,
           SUFFIX                         ,
           WORK_SCHEDULE                  ,
           PER_INFORMATION21              ,
           PER_INFORMATION22              ,
           PER_INFORMATION23              ,
           PER_INFORMATION24              ,
           PER_INFORMATION25              ,
           PER_INFORMATION26              ,
           PER_INFORMATION27              ,
           PER_INFORMATION28              ,
           PER_INFORMATION29              ,
           PER_INFORMATION30              ,
           OBJECT_VERSION_NUMBER          ,
           DATE_OF_DEATH                  ,
           REHIRE_REASON                  ,
           COORD_BEN_MED_PLN_NO           ,
           COORD_BEN_NO_CVG_FLAG          ,
           DPDNT_ADOPTION_DATE            ,
           DPDNT_VLNTRY_SVCE_FLAG         ,
           RECEIPT_OF_DEATH_CERT_DATE     ,
           USES_TOBACCO_FLAG              ,
           BENEFIT_GROUP_ID               ,
           ORIGINAL_DATE_OF_HIRE          ,
           TOWN_OF_BIRTH                  ,
           REGION_OF_BIRTH                ,
           COUNTRY_OF_BIRTH               ,
           GLOBAL_PERSON_ID               ,
           COORD_BEN_MED_PL_NAME          ,
           COORD_BEN_MED_INSR_CRR_NAME    ,
           COORD_BEN_MED_INSR_CRR_IDENT   ,
           COORD_BEN_MED_EXT_ER           ,
           COORD_BEN_MED_CVG_STRT_DT      ,
           COORD_BEN_MED_CVG_END_DT       ,
           PARTY_ID                       ,
           NPW_NUMBER                     ,
           CURRENT_NPW_FLAG   
         )
         values
         (
           l_person_id                        ,
           cur.EFFECTIVE_START_DATE           ,
           cur.EFFECTIVE_END_DATE             ,
           CAPBRIMP_MAPPING.GetBGMapping(cur.BUSINESS_GROUP_ID),
           cur.PERSON_TYPE_ID                 ,
           cur.LAST_NAME                      ,
           cur.START_DATE                     ,
           cur.APPLICANT_NUMBER               ,
           cur.COMMENT_ID                     ,
           cur.CURRENT_APPLICANT_FLAG         ,
           cur.CURRENT_EMP_OR_APL_FLAG        ,
           cur.CURRENT_EMPLOYEE_FLAG          ,
           cur.DATE_EMPLOYEE_DATA_VERIFIED    ,
           cur.DATE_OF_BIRTH                  ,
           cur.EMAIL_ADDRESS                  ,
           cur.EMPLOYEE_NUMBER                ,
           cur.EXPENSE_CHECK_SEND_TO_ADDRESS  ,
           cur.FIRST_NAME                     ,
           cur.FULL_NAME                      ,
           cur.KNOWN_AS                       ,
           cur.MARITAL_STATUS                 ,
           cur.MIDDLE_NAMES                   ,
           cur.NATIONALITY                    ,
           cur.NATIONAL_IDENTIFIER            ,
           cur.PREVIOUS_LAST_NAME             ,
           cur.REGISTERED_DISABLED_FLAG       ,
           cur.SEX                            ,
           cur.TITLE                          ,
           CAPPOIMP_MAPPING.GetVendorIDMapping(cur.VENDOR_ID),
           cur.WORK_TELEPHONE                 ,
           cur.REQUEST_ID                     ,
           cur.PROGRAM_APPLICATION_ID         ,
           cur.PROGRAM_ID                     ,
           cur.PROGRAM_UPDATE_DATE            ,
           cur.ATTRIBUTE_CATEGORY             ,
           cur.ATTRIBUTE1                     ,
           cur.ATTRIBUTE2                     ,
           cur.ATTRIBUTE3                     ,
           cur.ATTRIBUTE4                     ,
           cur.ATTRIBUTE5                     ,
           cur.ATTRIBUTE6                     ,
           cur.ATTRIBUTE7                     ,
           cur.ATTRIBUTE8                     ,
           cur.ATTRIBUTE9                     ,
           cur.ATTRIBUTE10                    ,
           cur.ATTRIBUTE11                    ,
           cur.ATTRIBUTE12                    ,
           cur.ATTRIBUTE13                    ,
           cur.ATTRIBUTE14                    ,
           cur.ATTRIBUTE15                    ,
           cur.ATTRIBUTE16                    ,
           cur.ATTRIBUTE17                    ,
           cur.ATTRIBUTE18                    ,
           cur.ATTRIBUTE19                    ,
           cur.ATTRIBUTE20                    ,
           cur.ATTRIBUTE21                    ,
           cur.ATTRIBUTE22                    ,
           cur.ATTRIBUTE23                    ,
           cur.ATTRIBUTE24                    ,
           cur.ATTRIBUTE25                    ,
           cur.ATTRIBUTE26                    ,
           cur.ATTRIBUTE27                    ,
           cur.ATTRIBUTE28                    ,
           cur.ATTRIBUTE29                    ,
           cur.ATTRIBUTE30                    ,
           cur.LAST_UPDATE_DATE               ,
           CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
           cur.LAST_UPDATE_LOGIN              ,
           CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
           cur.CREATION_DATE                  ,
           cur.PER_INFORMATION_CATEGORY       ,
           cur.PER_INFORMATION1               ,
           cur.PER_INFORMATION2               ,
           cur.PER_INFORMATION3               ,
           cur.PER_INFORMATION4               ,
           cur.PER_INFORMATION5               ,
           cur.PER_INFORMATION6               ,
           cur.PER_INFORMATION7               ,
           cur.PER_INFORMATION8               ,
           cur.PER_INFORMATION9               ,
           cur.PER_INFORMATION10              ,
           cur.PER_INFORMATION11              ,
           cur.PER_INFORMATION12              ,
           cur.PER_INFORMATION13              ,
           cur.PER_INFORMATION14              ,
           cur.PER_INFORMATION15              ,
           cur.PER_INFORMATION16              ,
           cur.PER_INFORMATION17              ,
           cur.PER_INFORMATION18              ,
           cur.PER_INFORMATION19              ,
           cur.PER_INFORMATION20              ,
           cur.BACKGROUND_CHECK_STATUS        ,
           cur.BACKGROUND_DATE_CHECK          ,
           cur.BLOOD_TYPE                     ,
           cur.CORRESPONDENCE_LANGUAGE        ,
           cur.FAST_PATH_EMPLOYEE             ,
           cur.FTE_CAPACITY                   ,
           cur.HOLD_APPLICANT_DATE_UNTIL      ,
           cur.HONORS                         ,
           cur.INTERNAL_LOCATION              ,
           cur.LAST_MEDICAL_TEST_BY           ,
           cur.LAST_MEDICAL_TEST_DATE         ,
           cur.MAILSTOP                       ,
           cur.OFFICE_NUMBER                  ,
           cur.ON_MILITARY_SERVICE            ,
           cur.ORDER_NAME                     ,
           cur.PRE_NAME_ADJUNCT               ,
           cur.PROJECTED_START_DATE           ,
           cur.REHIRE_AUTHORIZOR              ,
           cur.REHIRE_RECOMMENDATION          ,
           cur.RESUME_EXISTS                  ,
           cur.RESUME_LAST_UPDATED            ,
           cur.SECOND_PASSPORT_EXISTS         ,
           cur.STUDENT_STATUS                 ,
           cur.SUFFIX                         ,
           cur.WORK_SCHEDULE                  ,
           cur.PER_INFORMATION21              ,
           cur.PER_INFORMATION22              ,
           cur.PER_INFORMATION23              ,
           cur.PER_INFORMATION24              ,
           cur.PER_INFORMATION25              ,
           cur.PER_INFORMATION26              ,
           cur.PER_INFORMATION27              ,
           cur.PER_INFORMATION28              ,
           cur.PER_INFORMATION29              ,
           cur.PER_INFORMATION30              ,
           cur.OBJECT_VERSION_NUMBER          ,
           cur.DATE_OF_DEATH                  ,
           cur.REHIRE_REASON                  ,
           cur.COORD_BEN_MED_PLN_NO           ,
           cur.COORD_BEN_NO_CVG_FLAG          ,
           cur.DPDNT_ADOPTION_DATE            ,
           cur.DPDNT_VLNTRY_SVCE_FLAG         ,
           cur.RECEIPT_OF_DEATH_CERT_DATE     ,
           cur.USES_TOBACCO_FLAG              ,
           cur.BENEFIT_GROUP_ID               ,
           cur.ORIGINAL_DATE_OF_HIRE          ,
           cur.TOWN_OF_BIRTH                  ,
           cur.REGION_OF_BIRTH                ,
           cur.COUNTRY_OF_BIRTH               ,
           cur.GLOBAL_PERSON_ID               ,
           cur.COORD_BEN_MED_PL_NAME          ,
           cur.COORD_BEN_MED_INSR_CRR_NAME    ,
           cur.COORD_BEN_MED_INSR_CRR_IDENT   ,
           cur.COORD_BEN_MED_EXT_ER           ,
           cur.COORD_BEN_MED_CVG_STRT_DT      ,
           cur.COORD_BEN_MED_CVG_END_DT       ,
           CAPARIMP_MAPPING.GetHZPartyIDMapping(cur.PARTY_ID),
           cur.NPW_NUMBER                     ,
           cur.CURRENT_NPW_FLAG   
         );  
       exception
         when others then
       	 l_err_msg_tmp := sqlerrm;
       end;	  	    
       
       if l_err_msg_tmp IS NULL then
         l_processed_count := l_processed_count + 1;  
       else
         l_rejected_count := l_rejected_count + 1;
         l_err_msg := l_err_msg_tmp; 
       end if;
    
   end loop;  

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
      
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_PERSON', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
